package com.example.exam.db;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.lang.reflect.ParameterizedType;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public abstract class BaseDao<T, M> implements BaseSql {
	
	protected Log logger = LogFactory.getLog(getClass());
	
	@Resource
    private JdbcTemplate jtl;
	@Resource
    private NamedParameterJdbcTemplate namedJtl;
	
	/**
	 * 实体类属性Mapper
	 */
    protected RowMapper<T> propRowMapper = null;
    
    
    /**
     * 驼峰法则的Mapper <br/>
	 * 如：数据库字段my_teacher_name 转成 myTeacherName
     */
    protected RowMapper<Map<String, Object>> mapRowMapper = new RowMapper<Map<String,Object>>() {
		@Override
		public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
			StringBuilder camelKey = new StringBuilder();
			//System.out.println(camelKey);
			Map<String,Object> camelKeyMap = new HashMap<String,Object>();
	    		int column = rs.getMetaData().getColumnCount();
	    		for(int i=1;i<=column;i++){
	    			camelKey.delete(0, camelKey.length());
	    			String columnLabel = rs.getMetaData().getColumnLabel(i);
	    			if(!columnLabel.contains("_")){
	    				camelKeyMap.put(columnLabel, rs.getObject(i));
	    				continue;
	    			}
	    			for(int j=0,len = columnLabel.length();j<len;j++){
	    				char c = columnLabel.charAt(j);
	    				if(c != '_'){
	    					camelKey.append(c);
	    					continue;	
	    				}
	    				if(++j < len) 
	    					camelKey.append(Character.toUpperCase(columnLabel.charAt(j)));
	    			}
	    			camelKeyMap.put(camelKey.toString(), rs.getObject(i));
	    		}
	    		return camelKeyMap;
		}
	};
    
    @SuppressWarnings("unchecked")
	public BaseDao(){
    		Class <T> entityClass = (Class <T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
    		this.propRowMapper = new BeanPropertyRowMapper<>(entityClass);
    }
	
	public JdbcTemplate getJtl() {
		return jtl;
	}
	
	public NamedParameterJdbcTemplate getNamedJtl() {
		return namedJtl;
	}
	
    /**
     * 一页显示的记录数 
     */
    private static final int PAGE_PER_NUM = 15;

    
    /**
     * 根据id查询记录
     */
	public T findById(M id) {
		try {
			String sql = findByIdSql();
			if(StringUtils.isEmpty(sql)){
				logger.error("findByIdSql()未实现");
				return null;
			}
			T obj = jtl.queryForObject(sql, new Object[]{id}, propRowMapper);
			return obj;
		} catch (DataAccessException e) {
			return null;
		}
	}
	
    /**
     * 新增一条记录（使用实体类自动映射sql参数）
     */
	public int add(T t) {
		String sql = getAddSql();
		if(StringUtils.isEmpty(sql)){
			logger.error("getAddSql()未实现");
			return 0;
		}
		return namedJtl.update(sql, new BeanPropertySqlParameterSource(t));
	}
	
	/**
     * 新增一条记录，并且返回数据id
     */
	public int insertAndGetId(T t) {
		String sql = getAddSql();
		if(StringUtils.isEmpty(sql)){
			logger.error("getAddSql()未实现");
			return 0;
		}
		int id = 0;
		try {
			SqlParameterSource ps = new BeanPropertySqlParameterSource(t);
			KeyHolder keyholder = new GeneratedKeyHolder();
			namedJtl.update(sql, ps, keyholder);
			id = keyholder.getKey().intValue();
		} catch (InvalidDataAccessApiUsageException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return id;
	}
	
	/**
	 * 根据id更新一条记录（使用实体类自动映射sql参数,全部字段更新）
	 */
	public int updateAllFieldById(T t) {
		String sql = getUpdateAllFieldSql();
		if(StringUtils.isEmpty(sql)){
			logger.error("getUpdateAllFieldSql()未实现");
			return 0;
		}		
		int num = namedJtl.update(sql, new BeanPropertySqlParameterSource(t));
		return num;
	}
	
	/**
	 * 根据id删除一条记录（逻辑删除，更改数据状态）
	 */
	public int removeById(M id) {
		String sql = getRemoveByIdSql();
		if(StringUtils.isEmpty(sql)){
			logger.error("getRemoveByIdSql()未实现");
			return 0;
		}	
		return jtl.update(sql, id);
	}
	
	/**
	 * 根据id删除一条记录（物理删除）
	 */
	public int deleteById(M id) {
		String sql = getDeleteByIdSql();
		if(StringUtils.isEmpty(sql)){
			logger.error("getDeleteByIdSql()未实现");
			return 0;
		}
		return jtl.update(sql, id);
	}
    
    /**
     * 不分页查询返回list
     */
    public List<T> listForBean(String querySql, Map<String, Object> paramMap){
        return getNamedJtl().query(querySql, new MapSqlParameterSource(paramMap), propRowMapper);
    }
    
    /**
     * 不分页查询返回list
     */
    public List<Map<String, Object>> listForMap(String querySql, Map<String, Object> paramMap){
    		return getNamedJtl().query(querySql, new MapSqlParameterSource(paramMap), mapRowMapper);
    }
    
    /**
     * 返回以实体类作为数据封装的分页查询方法
     * @param paramMap 查询条件
	 * @param pageInfo 分页条件
     */
    public PageInfoBean pageForBean(String querySql, Map<String, Object> paramMap, PageInfoBean pageInfo){
    		return this.pageList(querySql, paramMap, pageInfo, true);
    }
    
    /**
     * 返回以map作为数据封装的分页查询方法
     * @param paramMap 查询条件
	 * @param pageInfo 分页条件
     */
    public PageInfoBean pageForMap(String querySql, Map<String, Object> paramMap, PageInfoBean pageInfo){
		return this.pageList(querySql, paramMap, pageInfo, false);
    }
    
    /**
     * 分页查询方法（sql参数以map形式存储）
	 * @param pageInfo 分页条件
	 * @param returnObj 返回的数据类型（true：实体类，false：map形式）
     */
    private PageInfoBean pageList(String querySql, Map<String, Object> paramMap, PageInfoBean pageInfo, boolean returnObj){
        //设置每页显示记录数
        if(pageInfo.getPerPage()==0){
            pageInfo.setPerPage(PAGE_PER_NUM);
        }
        //设置要显示的页数
        if(pageInfo.getCurPage()==0){
        		pageInfo.setCurPage(1);
        }
        //查询总记录数
		StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
		totalSQL.append(querySql);
		totalSQL.append(" ) totalTable ");
		String countSql = totalSQL.toString();
		if(paramMap==null){
			paramMap = new HashMap<String, Object>();
		}
		int countNum = getNamedJtl().queryForObject(countSql, paramMap, Integer.class);
		pageInfo.setTotalRec(countNum);
        //计算查询起至行号
        pageInfo.calPageParam();
        //查询结果集
        paramMap.put("startIndex", pageInfo.getStartIndex());
        paramMap.put("pageSize", pageInfo.getPerPage());
        querySql = this.buildPageSql(querySql);
		MapSqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
        if(returnObj){
        		//以实体类形式返回数据
        		List<T> recordList = getNamedJtl().query(querySql, paramSource, propRowMapper);
            pageInfo.setRecList(recordList);
        }else{
        		//返回map格式的数据
        		List<Map<String, Object>> recordList = getNamedJtl().query(querySql, paramSource, mapRowMapper);
        		pageInfo.setRecList(recordList);
        }
        return pageInfo;
    }
    
    /**
     * 查询满足条件的单条记录的实体对象，sql中参数占位符是:xxx格式
     */
    public T queryOneByCondition(String sql, Map<String, Object> paramMap){
		try {
			return getNamedJtl().queryForObject(sql, paramMap, propRowMapper);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
    }
    
    /**
     * 查询满足条件的单条记录的实体对象，sql中参数占位符是:xxx格式
     */
    public T queryOneByCondition(String sql, T condition){
		try {
			return (T) getNamedJtl().queryForObject(sql, new BeanPropertySqlParameterSource(condition), propRowMapper);
		} catch (DataAccessException e) {
			return null;
		}
    }
    
    /**
     * 根据条件，更新记录（可以是一条或多条数据，sql中参数占位符是:xxx格式）
     */
    public int updateByCondition(String sql, T condition){
    		SqlParameterSource params = new BeanPropertySqlParameterSource(condition);
		return getNamedJtl().update(sql, params);
    }
    
    /**
     * 统计查询返回数字
     */
    public int queryForInt(String sql, String ... args){
    		return getJtl().queryForObject(sql, args, Integer.class);
    }
    
    private static final String PAGE_SQL_MYSQL_TMP = "%s limit :startIndex, :pageSize ";
    /**
     * 构造分页查询语句
     */
    public String buildPageSql(String baseSql){
    		return String.format(PAGE_SQL_MYSQL_TMP, baseSql);
    }
    
	@SuppressWarnings("unchecked")
	public <N> List<N> queryForList(String sql, Class<N> tClass, Object... args) {
		RowMapper<N> rowMapper = null;
		if (Map.class.isAssignableFrom(tClass)) {
			rowMapper = (RowMapper<N>) new ColumnMapRowMapper();
		} else if (String.class.equals(tClass) || Integer.class.equals(tClass) 
                            || Long.class.equals(tClass)) {
			rowMapper = new SingleColumnRowMapper<N>(tClass);
		} else {
			rowMapper = new BeanPropertyRowMapper<N>(tClass);
		}
		List<N> list = jtl.query(sql, rowMapper, args);
		return list;
	}
    
	/**
	 * 查找结果为空返回null，不为空则返回集合第一个元素
	 */
	public <N> N queryForObject(String sql, Class<N> tClass, Object... args) {
		List<N> list = queryForList(sql, tClass, args);
		return list == null || list.isEmpty() ? null : list.get(0);
	}

}
